# -*- coding: utf-8 -*-
import sqlite3
import unicodedata

class CollectionMetaDataDB(object):
    def __init__(self, model):
        self.model = model
        self.connection = sqlite3.connect(":memory:")
        self.connection.create_collation("UTF8CI", self.UTF8_collate)
        self.cursor = self.connection.cursor()
        self.cursor.execute("""
                CREATE TABLE
                    metadata(
                        id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
                        title, 
                        artist, 
                        album, 
                        track, 
                        genre, 
                        time,
                        date, 
                        file);""")
    
    def UTF8_collate(self, str1, str2):
        # FIXME: nfc o nfd? non so, con nfd va, ma è una scelta fatta casualmente
        if isinstance(str1, str):
            str1 = str1.decode("utf8")
        if isinstance(str2, str):
            str2 = str2.decode("utf8")  
        return cmp(unicodedata.normalize("NFD", str1.lower()), 
                   unicodedata.normalize("NFD", str2.lower()))
    
    def clear_data(self):
            self.cursor.executescript("""
                 DELETE FROM metadata;
                 
                 DROP VIEW IF EXISTS metadataview;
                                """)

    def load_data(self, songs_list):
        for song in songs_list:
            item = {"title": None, "artist": None, "album": None, "track": None, 
                    "genre": None, "time": None, "date": None, "file": None}
            for k,v in song.iteritems():
                if k in item:
                    if isinstance(v, list):
                        v = v[0]
                    if k == "track":
                        v = v.split("/")[0]
                    item[k] = unicode(v, "utf-8")
            self.cursor.execute("""
                INSERT INTO 
                    metadata (title, artist, album, track, genre, time, date, file)
                VALUES 
                    (:title, :artist, :album, :track, :genre, :time, :date, :file);""", item)
        self.cursor.execute("""
            CREATE TEMP VIEW metadataview AS
                SELECT * FROM metadata ORDER BY artist COLLATE UTF8CI ASC                            
                            """)
        self.connection.commit()
    
    def create_new_view(self, text=""):
        # FIXME: se ordino quando è in corso una ricerca i dati sono reinseriti tutti
        self.cursor.execute("""
            DROP VIEW IF EXISTS metadataview;
                            """)
        it = ["CREATE TEMP VIEW metadataview AS SELECT * FROM metadata "]
        tagTypes = self.model.order_tagTypes
        if text != "":
            text = ''.join(["%", text.replace("'", "''"), "%"])
            it.extend(["WHERE title LIKE '", text])
            for tagType in tagTypes:
                it.extend(["' OR ", tagType, " LIKE '", text])
            it.append("'")
        it.extend([" ORDER BY ", tagTypes[0], " COLLATE UTF8CI "])          
        if self.model.data_order == "asc":
            it.append("ASC")
        else:
            it.append("DESC")
        it.append(";")
        query = ''.join(it)
        self.cursor.execute(query)
                            
    def get_level_0_data(self, tagType):
        query = ''.join(["SELECT DISTINCT ", tagType, " FROM metadataview;"])
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def get_level_1_data(self, tagType, tag):
        tagTypes = self.model.order_tagTypes
        it = []
        if tagType == "title":
            it.extend(["SELECT ", tagType]) 
        else:
            it.extend(["SELECT DISTINCT ", tagType])
        it.extend([ " FROM metadataview WHERE ", 
                   tagTypes[0], "='", tag.replace("'", "''"), "';"])
        query = ''.join(it)
        self.cursor.execute(query)
        return self.cursor.fetchall()   
    
    def get_level_2_data(self, tagType, tags):
        tagTypes = self.model.order_tagTypes
        it = []
        if tagType == "title":
            it.extend(["SELECT ", tagType]) 
        else:
            it.extend(["SELECT DISTINCT ", tagType])
        it.extend([ " FROM metadataview WHERE ", 
                   tagTypes[0], "='", tags[0].replace("'", "''"), "' AND ", 
                   tagTypes[1], "='", tags[1].replace("'", "''"), "';"])
        query = ''.join(it)
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def get_level_3_data(self, tags):
        tagTypes = self.model.order_tagTypes
        it = ["SELECT title FROM metadataview WHERE ", 
              tagTypes[0], "='", tags[0].replace("'", "''"), "' AND ", 
              tagTypes[1], "='", tags[1].replace("'", "''"), "' AND ",
              tagTypes[2], "='", tags[2].replace("'", "''"), "';"]
        query = ''.join(it)
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def level_0_files(self, seq):
        it = ["SELECT DISTINCT file FROM metadataview WHERE ", 
              seq[0][0], "='", seq[0][1].replace("'", "''"), "';"]
        query = ''.join(it)
        self.cursor.execute(query)
        return self.cursor.fetchall()  
    
    def level_1_files(self, seq):
        it = ["SELECT DISTINCT file FROM metadataview WHERE ", 
              seq[0][0], "='", seq[0][1].replace("'", "''"), "' AND ",
              seq[1][0], "='", seq[1][1].replace("'", "''"), "';"]
        query = ''.join(it)
        self.cursor.execute(query)
        return self.cursor.fetchall()     
            
    def level_2_files(self, seq):
        it = ["SELECT DISTINCT file FROM metadataview WHERE ", 
              seq[0][0], "='", seq[0][1].replace("'", "''"), "' AND ", 
              seq[1][0], "='", seq[1][1].replace("'", "''"), "' AND ", 
              seq[2][0], "='", seq[2][1].replace("'", "''"), "';"]
        query = ''.join(it)
        self.cursor.execute(query)
        return self.cursor.fetchall()  
            
    def level_3_files(self, seq):
        it = ["SELECT DISTINCT file FROM metadataview WHERE ", 
              seq[0][0], "='", seq[0][1].replace("'", "''"), "' AND ", 
              seq[1][0], "='", seq[1][1].replace("'", "''"), "' AND ", 
              seq[2][0], "='", seq[2][1].replace("'", "''"), "' AND ", 
              seq[3][0], "='", seq[3][1].replace("'", "''"), "';"]
        query = ''.join(it)
        self.cursor.execute(query)
        return self.cursor.fetchall()               
